package com.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author Khaled Lela
 */
public class PayerSql {

    private Connection con;
    private PreparedStatement ps;
    private ResultSet rs;
    private boolean error;
    private Statement statement;

    public PayerSql() {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby:BreadDB;");
            statement = con.createStatement();
        } catch (Exception ex) {
        }
    }

// get payer name to be displayed on payerNameTF
    public String getIDName(int id) {
        String name = null;
        try {
            ps = con.prepareStatement("SELECT PersonName "
                    + " FROM People "
                    + " WHERE "
                    + " PersonID = ? ");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                name = rs.getString("PersonName");
            }
        } catch (Exception e) {
        }
        return name;
    }

    //insert Payer data
    public boolean setPay(int id, int year, int month, int day) {
        try {
            ps = con.prepareStatement("insert into Payer(PersonID,PayYear,PayMonth,PayDay) "
                    + "values"
                    + " (?,?,?,?)");
            ps.setInt(1, id);
            ps.setInt(2, year);
            ps.setInt(3, month);
            ps.setInt(4, day);
            ps.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
            error = true;
        }
        return error;
    }

    public Map<Integer, Integer> getPaiedMonths(int id, int year) {
        Map<Integer, Integer> monthDay = new HashMap<Integer, Integer>();
        try {
            ps = con.prepareStatement("SELECT PayMonth , PayDay"
                    + " FROM Payer "
                    + " WHERE "
                    + " PersonID = ? AND PayYear = ?");
            ps.setInt(1, id);
            ps.setInt(2, year);
            rs = ps.executeQuery();
            while (rs.next()) {
                monthDay.put(rs.getInt("PayMonth"), rs.getInt("PayDay"));
            }
        } catch (Exception e) {
        }
        return monthDay;
    }
}
